package com.apes.fn.scm.contract;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * param:zyc
 **/
public interface FnContractRepository extends CustomRepository<FnContract, String> {

    @Query(value = "SELECT * FROM FN_CONTRACT WHERE PARTNER_ID = :companyId AND TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) and state in ('approve', 'extension', 'activation')", nativeQuery = true)
    List<FnContract> qryValidFnContract(@Param("companyId") String companyId);

    @Query(value = "select a from FnContract a where a.state ='approve' and (a.partner.id = :partner or a.partner.party.name like %:companyId%) ")
    List<FnContract> findByPartner(String partner,String companyId);


    @Query(value = "select a from FnContract a where a.state ='approve' and a.company.id = :companyId and (a.partner.id like %:partner% or a.partner.party.name like %:partner%) ")
    List<FnContract> findPartner(String partner,String companyId);

    @Query(value = "SELECT A.* FROM fn_contract A WHERE A.STATE ='approve' AND A.PARTNER_ID = :partner AND A.VALID_PERIOD > :date AND A.VALID_FROM < :date",nativeQuery = true)
    FnContract findCompany(@Param("partner") String partner, @Param("date") Date date);

    @Query(value = "SELECT A.* FROM fn_contract A WHERE A.STATE ='approve' AND A.PARTNER_ID = :partner and rownum = 1",nativeQuery = true)
    FnContract  findByPartnerAndState(@Param("partner") String partner);

    @Query(value = "SELECT A.* FROM FN_CONTRACT A WHERE A.COMPANY_ID = :company AND A.STATE = 'approve' AND TRUNC(A.VALID_PERIOD) >= TRUNC(:reconcileEndDate)",nativeQuery = true)
    List<FnContract> findByCompany(@Param("company") String company,@Param("reconcileEndDate") Date reconcileEndDate);
}
